INSERT INTO NodeType VALUES('Authorization', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.Authentication.AuthenticationProviderNode')
GO

INSERT INTO Node VALUES((SELECT ID FROM NodeType WHERE TypeName = 'openXDA.Nodes.Types.Authentication.AuthenticationProviderNode'), NULL, 'SSO Provider', 1)
GO

EXEC sp_rename 'AssetType', AssetType_old
GO

CREATE TABLE AssetType
(
    ID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Description VARCHAR(MAX) NULL
)
GO

INSERT INTO AssetType (ID, Name, Description) VALUES (1,'Line','Transmission Line')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (2,'Bus','Bus')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (3,'Breaker','Breaker')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (4,'CapacitorBank','Bank of Capacitors')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (5,'LineSegment','Segment of a Transmission Line')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (6,'Transformer','Transformer')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (7,'CapacitorBankRelay','Relay for a Capacitor Bank')
GO
INSERT INTO AssetType (ID, Name, Description) VALUES (8,'DER','DER governed by IEEE Standard 1547-2018')
GO

DECLARE @tableName SYSNAME
DECLARE @columnName SYSNAME
DECLARE @constraintName SYSNAME
DECLARE @sql NVARCHAR(MAX)

SET @tableName = 'Asset'
SET @columnName = 'AssetTypeID'
SET @constraintName = (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @tableName AND COLUMN_NAME = @columnName)
SET @sql = N'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @constraintName
EXEC sp_executesql @sql

SET @tableName = 'AssetRelationshipTypeAssetType'
SET @columnName = 'AssetTypeID'
SET @constraintName = (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @tableName AND COLUMN_NAME = @columnName)
SET @sql = N'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @constraintName
EXEC sp_executesql @sql

ALTER TABLE Asset
ADD FOREIGN KEY(AssetTypeID)
REFERENCES AssetType(ID)
GO

ALTER TABLE AssetRelationshipTypeAssetType
ADD FOREIGN KEY(AssetTypeID)
REFERENCES AssetType(ID)
GO

ALTER TABLE BreakerAttributes
ADD AirGapResistor BIT NOT NULL DEFAULT(0)
GO
 
CREATE TABLE DERAttributes (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    AssetID INT NOT NULL REFERENCES Asset(ID),
    FullRatedOutputCurrent FLOAT NOT NULL,
    VoltageLevel VARCHAR(6) NOT NULL
)
GO

ALTER VIEW Breaker AS
    SELECT 
        AssetID AS ID,
        AssetKey,
        VoltageKV,
        ThermalRating,
        Speed,
        Description,
        AssetName,
        AssetTypeID,
        TripTime,
        PickupTime,
        TripCoilCondition,
        Spare,
        AirGapResistor
    FROM Asset JOIN BreakerAttributes ON Asset.ID = BreakerAttributes.AssetID
GO

DROP TRIGGER TR_INSERT_Breaker
GO

CREATE TRIGGER TR_INSERT_Breaker ON Breaker
INSTEAD OF INSERT AS 
BEGIN
    INSERT INTO Asset (AssetKey, AssetTypeID, Description, VoltageKV, AssetName, Spare)
        SELECT 
            AssetKey AS AssetKey,
            (SELECT ID FROM AssetType WHERE Name = 'Breaker') AS AssetTypeID,
            Description AS Description,
            VoltageKV AS VoltageKV,
            AssetName AS AssetName,
            Spare AS Spare
    FROM INSERTED

    INSERT INTO BreakerAttributes (AssetID, ThermalRating, Speed, TripTime, PickupTime, TripCoilCondition, AirGapResistor)
        SELECT 
            (SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
            ThermalRating AS ThermalRating,
            Speed AS Speed,
            TripTime AS TripTime,
            PickupTime AS PickupTime,
            TripCoilCondition AS TripCoilCondition,
            AirGapResistor AS AirGapResistor
    FROM INSERTED

END
GO

DROP TRIGGER TR_UPDATE_Breaker
GO

CREATE TRIGGER TR_UPDATE_Breaker ON Breaker
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE (VoltageKV) OR UPDATE(Spare))
    BEGIN
        UPDATE Asset
        SET
            Asset.AssetKey = INSERTED.AssetKey,
            Asset.Description = INSERTED.Description,
            Asset.AssetName = INSERTED.AssetName,
            Asset.VoltageKV = INSERTED.VoltageKV,
            Asset.Spare = INSERTED.Spare
        FROM
            ASSET 
        INNER JOIN
            INSERTED
        ON 
            INSERTED.ID = ASSET.ID;
    END
    UPDATE BreakerAttributes
        SET
            BreakerAttributes.ThermalRating = INSERTED.ThermalRating,
            BreakerAttributes.Speed = INSERTED.Speed,
            BreakerAttributes.TripTime = INSERTED.TripTime,
            BreakerAttributes.PickupTime = INSERTED.PickupTime,
            BreakerAttributes.TripCoilCondition = INSERTED.TripCoilCondition,
            BreakerAttributes.AirGapResistor = INSERTED.AirGapResistor
        FROM
            BreakerAttributes 
    INNER JOIN
        INSERTED
    ON 
        INSERTED.ID = BreakerAttributes.AssetID;
END
GO

CREATE VIEW DER AS
    SELECT 
        AssetID AS ID,
        VoltageLevel,
        FullRatedOutputCurrent,
        AssetKey,
        VoltageKV,
        Description,
        AssetName,
        AssetTypeID,
        Spare
    FROM Asset JOIN DERAttributes ON Asset.ID = DERAttributes.AssetID
GO


CREATE TRIGGER TR_INSERT_DER ON DER
INSTEAD OF INSERT AS 
BEGIN
    INSERT INTO Asset (AssetKey, AssetTypeID, Description, AssetName, VoltageKV, Spare)
        SELECT 
            AssetKey AS AssetKey,
            (SELECT ID FROM AssetType WHERE Name = 'DER') AS AssetTypeID,
            Description AS Description,
            AssetName AS AssetName,
            VoltageKV AS VoltageKV,
            Spare AS Spare
    FROM INSERTED

    INSERT INTO DERAttributes (AssetID, FullRatedOutputCurrent, VoltageLevel )
        SELECT 
            (SELECT ID FROM Asset WHERE AssetKey = INSERTED.AssetKey) AS AssetID,
            FullRatedOutputCurrent AS FullRatedOutputCurrent,
            VoltageLevel AS VoltageLevel
    FROM INSERTED

END
GO

CREATE TRIGGER TR_UPDATE_DER ON DER
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE(VoltageKV) OR UPDATE(Spare))
    BEGIN
        UPDATE Asset
        SET
            Asset.AssetKey = INSERTED.AssetKey,
            Asset.Description = INSERTED.Description,
            Asset.AssetName = INSERTED.AssetName,
            Asset.VoltageKV = INSERTED.VoltageKV,
            Asset.Spare = INSERTED.Spare
        FROM
            ASSET 
        INNER JOIN
            INSERTED
        ON 
            INSERTED.ID = ASSET.ID;
    END
    UPDATE DERAttributes
        SET
            DERAttributes.FullRatedOutputCurrent = INSERTED.FullRatedOutputCurrent,
            DERAttributes.VoltageLevel = INSERTED.VoltageLevel
        FROM
            DERAttributes 
    INNER JOIN
        INSERTED
    ON 
        INSERTED.ID = DERAttributes.AssetID;
END
GO
 
CREATE TABLE ApplicationNode (
    ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    Name VARCHAR(200) NOT NULL
)
GO

INSERT INTO ApplicationNode VALUES ('00000000-0000-0000-0000-000000000000','OpenXDA')
GO
 
CREATE TABLE ApplicationSustainedUser (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    ApplicationNodeID  UNIQUEIDENTIFIER NOT NULL REFERENCES ApplicationNode(ID),
    UserAccountID  UNIQUEIDENTIFIER NOT NULL REFERENCES UserAccount(ID),
    Code VARCHAR(200) NOT NULL
)
GO

CREATE TABLE EventWorstDisturbance (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    WorstDisturbanceID INT NOT NULL REFERENCES Disturbance(ID),
    WorstLLDisturbanceID INT NULL REFERENCES Disturbance(ID),
    WorstLNDisturbanceID  INT NULL REFERENCES Disturbance(ID)
)
GO

CREATE NONCLUSTERED INDEX IX_EventWorstDisturbance_EventID
ON EventWorstDisturbance ( EventID ASC )
GO

CREATE TABLE VaisalaExtendedLightningData
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    LightningStrikeID INT NOT NULL REFERENCES LightningStrike(ID),
    PeakCurrent SMALLINT NOT NULL,
    FlashMultiplicity TINYINT NOT NULL,
    ParticipatingSensors TINYINT NOT NULL,
    DegreesOfFreedom TINYINT NOT NULL,
    EllipseAngle FLOAT NOT NULL,
    SemiMajorAxisLength FLOAT NOT NULL,
    SemiMinorAxisLength FLOAT NOT NULL,
    ChiSquared FLOAT NOT NULL,
    Risetime FLOAT NOT NULL,
    PeakToZeroTime FLOAT NOT NULL,
    MaximumRateOfRise FLOAT NOT NULL,
    CloudIndicator BIT NOT NULL,
    AngleIndicator BIT NOT NULL,
    SignalIndicator BIT NOT NULL,
    TimingIndicator BIT NOT NULL
)
GO

INSERT INTO [NoteApplication] (Name) VALUES ('SEbrowser')
GO

ALTER TABLE RelayPerformance
ADD TripCoilConditionTime INT NULL
GO
ALTER TABLE RelayPerformance
ADD ExtinctionTimeA INT NULL
GO
ALTER TABLE RelayPerformance
ADD ExtinctionTimeB INT NULL
GO
ALTER TABLE RelayPerformance
ADD ExtinctionTimeC INT NULL
GO
ALTER TABLE RelayPerformance
ADD I2CA FLOAT NULL
GO
ALTER TABLE RelayPerformance
ADD I2CB FLOAT NULL
GO
ALTER TABLE RelayPerformance
ADD I2CC FLOAT NULL
GO

ALTER VIEW BreakerHistory
AS
SELECT  Breaker.ID AS BreakerID,
	RelayPerformance.EventID AS EventID,
        RelayPerformance.Imax1,
	RelayPerformance.Imax2,
	RelayPerformance.TripInitiate,
	RelayPerformance.TripTime,
	RelayPerformance.PickupTime,
	RelayPerformance.TripCoilCondition,
	Breaker.TripCoilCondition AS TripCoilConditionAlert,
	Breaker.TripTime AS TripTimeAlert,
	Breaker.PickupTime AS PickupTimeAlert,
	RelayPerformance.ChannelID AS TripCoilChannelID,
        RelayPerformance.Tmax1,
        RelayPerformance.TplungerLatch,
        RelayPerformance.IplungerLatch,
        RelayPerformance.Idrop,
        RelayPerformance.TiDrop,
        RelayPerformance.Tend,
        RelayPerformance.TripTimeCurrent, 
        RelayPerformance.PickupTimeCurrent,
	RelayPerformance.TripCoilConditionTime,
        RelayPerformance.ExtinctionTimeA,
      	RelayPerformance.ExtinctionTimeB,
      	RelayPerformance.ExtinctionTimeC,
      	RelayPerformance.I2CA,
      	RelayPerformance.I2CB,
      	RelayPerformance.I2CC,
	COALESCE((SELECT TOP 1 ET.Name FROM 
		Event EV LEFT JOIN EventType ET ON Ev.EventTypeID = ET.ID WHERE EV.StartTime = Event.StartTime AND ET.Name <> 'Other' AND Ev.AssetID IN (SELECT ParentID FROM AssetConnection WHERE ChildID = Breaker.ID UNION SELECT ChildID FROM AssetConnection WHERE ParentID = Breaker.ID)
		),'Other') AS EventType
    FROM    RelayPerformance LEFT OUTER JOIN
            Channel ON RelayPerformance.ChannelID = Channel.ID LEFT OUTER JOIN
            Breaker ON Breaker.ID = Channel.AssetID LEFT JOIN
			Event ON Event.ID = RelayPerformance.EventID
GO

CREATE TABLE StandardMagDurCurve(
    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    XHigh float NOT NULL,
    XLow float NOT NULL,
    YHigh float NOT NULL,
    YLow float NOT NULL,
    UpperCurve Geometry NULL,
    LowerCurve Geometry NULL,
    Area Geometry NULL
)
GO

INSERT StandardMagDurCurve (Name, XHigh,XLow,YHigh,YLow, LowerCurve, UpperCurve, Area) VALUES (N'ITIC', 100, 0.000001,5,0, NULL, NULL, NULL)
GO
INSERT StandardMagDurCurve (Name, XHigh,XLow,YHigh,YLow, LowerCurve, UpperCurve, Area) VALUES (N'SEMI F47',1, 0.05, 1,0, NULL, NULL, NULL)
GO
INSERT StandardMagDurCurve (Name, XHigh,XLow,YHigh,YLow, LowerCurve, UpperCurve, Area) VALUES (N'IEEE 1668 Type I & II', 3,0.01, 1.2,0, NULL, NULL, NULL)
GO
INSERT StandardMagDurCurve (Name, XHigh,XLow,YHigh,YLow, LowerCurve, UpperCurve, Area) VALUES (N'IEEE 1668 Type III', 3,0.01, 1.2,0, NULL, NULL, NULL)
GO
INSERT StandardMagDurCurve (Name, XHigh,XLow,YHigh,YLow, LowerCurve, UpperCurve, Area) VALUES (N'NERC PRC-024-2', 4,0.001,1.3,0, NULL, NULL, NULL)
GO

UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0.5, 0.2 0.5, 0.2 0.7, 0.5 0.7,0.5 0.8,2 0.8,2 1.0,0.01 1.0, 0.01 0.5))' WHERE Name = 'IEEE 1668 Type I & II'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.01 0.5, 0.05 0.5, 0.05 0.7, 0.1 0.7,0.1 0.8,2 1.0,0.01 1.0, 0.01 0.5))' WHERE Name = 'IEEE 1668 Type III'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.05 0.5, 0.2 0.5, 0.2 0.7, 0.5 0.7,0.5 0.8,1 0.8, 10 0.8, 10 0, 0 0, 0 0.5, 0.05 0.5))' WHERE Name = 'SEMI F47'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.0001667 5, 0.001 2, 0.003 1.4, 0.003 1.2,0.5 1.2,0.5 1.1, 100 1.1,100 0.9, 10 0.9, 10 0.8, 0.5 0.8, 0.5 0.7, 0.02 0.7, 0.02 0, 1000 0, 1000 5, 0.0001667 5))' WHERE Name = 'ITIC'
GO
UPDATE StandardMagDurCurve SET Area = 'POLYGON((0.001 1.2, 0.2 1.2, 0.2 1.175, 0.5 1.175,0.5 1.15,1 1.15, 1 1.10,4 1.10, 4 0.9, 3 0.9, 3 0.75, 2 0.75, 2 0.65, 0.3 0.65, 0.3 0.45, 0.15 0.45, 0.15 0, 0.001 0, 0.001 1.2))' WHERE Name = 'NERC PRC-024-2'
GO

INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
VALUES ('Transformer-(Single)Breaker','Currents, Voltage and Breaker Status are passed across this connection.',1,'SELECT (CASE WHEN (SELECT MeasurementTypeID FROM Channel WHERE ID = {ChannelID}) IN (1,2,5) THEN 1 ELSE 0 END)','SELECT 1')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
VALUES ('CapBank-(Single)Breaker','Currents, Voltage and Breaker Status are passed across this connection.',1,'SELECT (CASE WHEN (SELECT MeasurementTypeID FROM Channel WHERE ID = {ChannelID}) IN (1,2,5) THEN 1 ELSE 0 END)','SELECT 1')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
VALUES ('Bus-Transformer','only Voltages are passed across this connection.',1,'SELECT (CASE WHEN (SELECT MeasurementTypeID FROM Channel WHERE ID = {ChannelID}) = 1 THEN 1 ELSE 0 END)','SELECT 0')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
VALUES ('Line-Transformer','only Voltages are passed across this connection.',1,'SELECT (CASE WHEN (SELECT MeasurementTypeID FROM Channel WHERE ID = {ChannelID}) = 1 THEN 1 ELSE 0 END)','SELECT 0')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
VALUES ('Line-CapBank','only Voltages are passed across this connection.',1,'SELECT (CASE WHEN (SELECT MeasurementTypeID FROM Channel WHERE ID = {ChannelID}) = 1 THEN 1 ELSE 0 END)','SELECT 0')
GO


INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
VALUES ('DER-(Single)Breaker','Currents, Voltage and Breaker Status are passed across this connection.',1,'SELECT (CASE WHEN (SELECT MeasurementTypeID FROM Channel WHERE ID = {ChannelID}) IN (1,2,5) THEN 1 ELSE 0 END)','SELECT 1')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
VALUES ('Line-DER','only Voltages are passed across this connection.',1,'SELECT (CASE WHEN (SELECT MeasurementTypeID FROM Channel WHERE ID = {ChannelID}) = 1 THEN 1 ELSE 0 END)','SELECT 0')
GO
INSERT INTO AssetRelationshipType ( Name, Description, BiDirectional, JumpConnection, PassThrough)
VALUES ('DER-Transformer','only Voltages are passed across this connection.',1,'SELECT (CASE WHEN (SELECT MeasurementTypeID FROM Channel WHERE ID = {ChannelID}) = 1 THEN 1 ELSE 0 END)','SELECT 0')
GO

INSERT INTO AssetRelationshipTypeAssetType (AssetRelationshipTypeID, AssetTypeID ) VALUES
    ((SELECT ID FROM AssetRelationshipType where Name = 'Transformer-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Transformer')),
    ((SELECT ID FROM AssetRelationshipType where Name = 'CapBank-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank')),
    ((SELECT ID FROM AssetRelationshipType where Name = 'Bus-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Line')),
    ((SELECT ID FROM AssetRelationshipType where Name = 'Line-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Line')),
    ((SELECT ID FROM AssetRelationshipType where Name = 'Line-CapBank'),(SELECT ID FROM AssetType WHERE Name = 'Line')),
    ((SELECT ID FROM AssetRelationshipType where Name = 'DER-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'DER')),
    ((SELECT ID FROM AssetRelationshipType where Name = 'Line-DER'),(SELECT ID FROM AssetType WHERE Name = 'Line')),
    ((SELECT ID FROM AssetRelationshipType where Name = 'DER-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'DER'))
GO

INSERT INTO AssetRelationshipTypeAssetType (AssetRelationshipTypeID, AssetTypeID ) VALUES
	((SELECT ID FROM AssetRelationshipType where Name = 'Transformer-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM AssetRelationshipType where Name = 'CapBank-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Bus-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Transformer')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Line-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Transformer')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Line-CapBank'),(SELECT ID FROM AssetType WHERE Name = 'CapacitorBank')),
	((SELECT ID FROM AssetRelationshipType where Name = 'DER-(Single)Breaker'),(SELECT ID FROM AssetType WHERE Name = 'Breaker')),
	((SELECT ID FROM AssetRelationshipType where Name = 'Line-DER'),(SELECT ID FROM AssetType WHERE Name = 'DER')),
	((SELECT ID FROM AssetRelationshipType where Name = 'DER-Transformer'),(SELECT ID FROM AssetType WHERE Name = 'Transformer'))
GO

EXEC sp_rename '[SystemCenter.AdditionalUserField]', AdditionalUserField
GO

EXEC sp_rename '[SystemCenter.AdditionalUserFieldValue]', AdditionalUserFieldValue
GO

---------------- SEBrowser TableSpace -------------
CREATE TABLE [SEBrowser.Setting]
(
   	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Scope] [varchar](64) NULL,
	[Name] [varchar](64) NULL,
	[Value] [varchar](512) NULL,
	[ApplicationInstance] [bit] NOT NULL,
	[Roles] [varchar](200) NULL,
)
GO

/*
	Required Fields are:
		EventID for matching,
		DisturbanceID for matching
		Time for showing the Time of the Event,
		Asset
	Everything else can be customized to appear in the UI.
*/

CREATE VIEW [dbo].[SEBrowser.EventSearchEventView] AS
	SELECT
		Event.ID AS EventID,
		FORMAT(Event.StartTime,'MM/dd/yyyy <br> HH:mm:ss.fffffff') AS Time,
		Meter.AssetKey AS [Meter Key],
		Meter.Name AS [Meter],
		Meter.Alias AS [Meter Alias],
		Meter.ShortName AS [Meter ShortName],
		Meter.Make AS [Meter Make],
		Meter.Model AS [Meter Model],
		Meter.TimeZone AS [Meter TimeZone],
		Meter.Description AS [Meter Desc],
		--Meter Sector
--Meter Firmware Version
--Meter Template Version
--Meter Connection Type
		Location.Name AS [Station],
		Location.LocationKey AS [Station Key],
		Location.ShortName AS [Station ShortName],
		Location.Alias AS [Station Alias],
		Location.Description AS [Station Desc],
--TSC
		Asset.AssetName AS [Asset Name],
		AssetType.Name AS [Asset Type],
		Asset.VoltageKV AS [Nom Voltage (kV)],
		Asset.Description AS [Asset Desc]
--Asset Manufacturer
--Asset Model
	FROM Event LEFT JOIN
		Meter ON Meter.ID = Event.MeterID LEFT JOIN 
		Location ON Meter.LocationID = Location.ID LEFT JOIN
		Asset ON Asset.ID = Event.AssetID LEFT JOIN
		AssetType ON Asset.AssetTypeID = AssetType.ID
GO

CREATE VIEW [dbo].[SEBrowser.EventSearchDetailsView] AS
	SELECT
		EventWorstDisturbance.EventID AS EventID,
		NULL AS FaultID,
		Disturbance.ID AS DisturbanceID,
		Phase.Name AS [Phase],
		FORMAT(Disturbance.DurationCycles, 'F2') AS [Duration (cycles)],
		Format(Disturbance.DurationSeconds, 'F4') AS [Duration (sec)],
		Disturbance.PerUnitMagnitude AS [MagDurMagnitude],
		Disturbance.DurationSeconds AS [MagDurDuration],
		Format((SELECT D.PerUnitMagnitude FROM Disturbance D WHERE D.ID = EventWorstDisturbance.WorstLLDisturbanceID )*100.0,'F2') as [Worst LL Magnitude (%nominal)],
        Format((SELECT D.PerUnitMagnitude FROM Disturbance D WHERE D.ID = EventWorstDisturbance.WorstLNDisturbanceID )*100.0,'F2')  as [Worst LN Magnitude (%nominal)],
		EventType.Name AS [Event Type],
		NULL AS [Fault Dist Alg],
		NULL AS [Fault Dist],
		NULL AS [Fault Current Mag],
		NULL AS [Fault Inception]
	FROM EventWorstDisturbance  LEFT JOIN
		Disturbance ON EventWorstDisturbance.WorstDisturbanceID = Disturbance.ID LEFT JOIN
		Phase ON Disturbance.PhaseID = Phase.ID LEFT JOIN
		EventType ON Disturbance.EventTypeID = EventType.ID
	UNION ALL
	SELECT 
		Event.ID AS EventID,
		NULL AS FaultID,
		NULL AS DisturbanceID,
		NULL AS [Phase],
		NULL AS [Duration (cycles)],
		NULL AS [Duration (sec)],
		NULL AS [MagDurMagnitude],
		NULL AS [MagDurDuration],
		NULL as [Worst LL Magnitude (%nominal)],
        NULL  as [Worst LN Magnitude (%nominal)],
		EventType.Name AS [Event Type],
		NULL AS [Fault Dist Alg],
		NULL AS [Fault Dist],
		NULL AS [Fault Current Mag],
		NULL AS [Fault Inception]
	FROM Event LEFT JOIN
		EventType ON EventType.ID = Event.EventTypeID 
	WHERE EventTypeID IN (SELECT ID FROM EventType WHERE Name IN ('BreakerOpen','Other'))
	UNION ALL
	SELECT 
		Event.ID AS EventID,
		FaultSummary.FaultNumber AS FaultID,
		NULL AS DisturbanceID,
		FaultSummary.FaultType AS [Phase],
		FORMAT(FaultSummary.DurationCycles, 'F2')  AS [Duration (cycles)],
		FORMAT(FaultSummary.DurationSeconds, 'F4')  AS [Duration (sec)],
		NULL AS [MagDurMagnitude],
		NULL AS [MagDurDuration],
		NULL as [Worst LL Magnitude (%nominal)],
        NULL  as [Worst LN Magnitude (%nominal)],
		EventType.Name AS [Event Type],
		FaultSummary.Algorithm AS [Fault Dist Alg],
		FORMAT(FaultSummary.Distance, 'F2') AS [Fault Dist],
		FORMAT(FaultSummary.CurrentMagnitude, 'F0') AS [Fault Current Mag],
		FORMAT(FaultSummary.Inception,'HH:mm:ss.fffffff') AS [Fault Inception]
	FROM Event LEFT JOIN
		EventType ON EventType.ID = Event.EventTypeID LEFT JOIN
		FaultSummary ON FaultSummary.IsSelectedAlgorithm <> 0 AND
			FaultSummary.IsValid <> 0 AND FaultSummary.IsSuppressed = 0 AND
			FaultSummary.EventID = Event.ID
	WHERE EventTypeID IN (SELECT ID FROM EventType WHERE Name IN ('Fault','RecloseIntoFault'))
GO



INSERT [dbo].[SEBrowser.Setting] ([Scope], [Name], [Value], [ApplicationInstance], [Roles]) VALUES (N'app.setting', N'applicationName', N'SEBrowser', 0, N'Administrator')
GO
INSERT [dbo].[SEBrowser.Setting] ([Scope], [Name], [Value], [ApplicationInstance], [Roles]) VALUES (N'app.setting', N'applicationDescription', N'System Event Browser', 0, N'Administrator')
GO
INSERT [dbo].[SEBrowser.Setting] ([Scope], [Name], [Value], [ApplicationInstance], [Roles]) VALUES (N'app.setting', N'applicationKeywords', N'open source, utility, browser, power quality, management', 0, N'Administrator')
GO
INSERT [dbo].[SEBrowser.Setting] ([Scope], [Name], [Value], [ApplicationInstance], [Roles]) VALUES (N'app.setting', N'bootstrapTheme', N'~/Content/bootstrap-theme.css', 0, N'Administrator')
GO
INSERT [dbo].[SEBrowser.Setting] ([Scope], [Name], [Value], [ApplicationInstance], [Roles]) VALUES (N'app.setting', N'XDAInstance', N'http://localhost:8989', 0, N'Administrator')
GO
INSERT [dbo].[SEBrowser.Setting] ([Scope], [Name], [Value], [ApplicationInstance], [Roles]) VALUES (N'app.setting', N'SCInstance', N'http://localhost:8987', 0, N'Administrator')
GO
INSERT [dbo].[SEBrowser.Setting] ([Scope], [Name], [Value], [ApplicationInstance], [Roles]) VALUES (N'app.setting', N'OpenSEEInstance', N'http://localhost/OpenSEE', 0, N'Administrator')
GO
INSERT [dbo].[SEBrowser.Setting]([Scope], [Name], [Value], [ApplicationInstance], [Roles]) VALUES (N'eventPreviewPane.widgetSetting', N'OpenSEEInstance', N'http://localhost/OpenSEE', 0, N'Administrator')
GO


Insert into ValueListGroup (Name, Description) VALUES('CustomReports', 'Custom Reports to list on the SEBrowser nav bar')
GO
Insert into ValueList (GroupID, Value, AltValue, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'CustomReports'), 'Breaker Report', 'breakerreport',1)
GO
Insert into ValueList (GroupID, Value, AltValue, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'CustomReports'), 'TripCoil Report', 'relayreport',2)
GO
Insert into ValueList (GroupID, Value, AltValue, SortOrder) VALUES((SELECT ID FROM ValueListGroup WHERE name = 'CustomReports'), 'CapBank Report', 'capbankreport',3)
GO


CREATE TABLE [SEBrowser.EventPreviewPaneSetting](
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(200) NOT NULL,
	Show bit NOT NULL DEFAULT (1),
	OrderBy int NOT NULL
)

INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchOpenSEE', 1,1)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchFaultSegments', 1,2)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchAssetVoltageDisturbances', 1,3)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchCorrelatedSags', 1,4)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('TVAESRIMap', 0,5)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchFileInfo', 0,6)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchHistory', 1,7)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchRelayPerformance', 0,8)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchBreakerPerformance', 0,9)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchNoteWindow', 1,11)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('TVALightning', 0,12)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('TVAFaultInfo', 1,13)
GO
INSERT [SEBrowser.EventPreviewPaneSetting] (Name, Show, OrderBy) VALUES ('EventSearchCapBankAnalyticOverview', 0,10)
GO

CREATE TABLE [SEBrowser.Links] (
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(100) NOT NULL UNIQUE,
	Display varchar(100) NOT NULL,
	Value varchar(max) NOT NULL
)
GO

INSERT INTO [SEBrowser.Links] (Name, Display,Value) VALUES
	('Breaker Report',0,'breakerreport'),
	('TCE  Report',0,'relayreport'),
	('CapBank Report',0,'capbankreport')
GO